﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using HQPad.DAL;
using HQPad.Models;


namespace HQPad.Services
{
    public interface IImportService
    {
        ImportResult ImportSkills(IUnitOfWork context, string spreadsheetPath, bool breakOnFailedRow, int ruleSetId);
        ImportResult ImportSpells(IUnitOfWork context, string spreadsheetPath, bool breakOnFailedRow, int ruleSetId);
        ImportResult ImportInvocations(IUnitOfWork context, string spreadsheetPath, bool breakOnFailedRow, int ruleSetId);
        ImportResult ImportGuilds(IUnitOfWork context, string spreadsheetPath, bool breakOnFailedRow, int ruleSetId);
    }

    public class ImportService : IImportService
    {
        private HQContext context = null;

        public ImportService(IUnitOfWork context)
        {
            this.context = (HQContext)context;
        }

        #region ImportGuilds
        public ImportResult ImportGuilds(IUnitOfWork context, string spreadsheetPath, bool breakOnFailedRow, int ruleSetId)
        {
            ImportResult result = new ImportResult();

            // Check the file exists.
            if (!File.Exists(spreadsheetPath))
            {
                result.Errors.Add(string.Format("The import file {0} does not exist.", spreadsheetPath));
                return result;
            }

            // Get the spreadsheet data from the first sheet.
            ExcelRepository excel = new ExcelRepository();

            DataSet sheetData = excel.ReadFile(spreadsheetPath);

            IGuildService guildService = new GuildService(context);

            int rowIndex = 0;

            List<AllowedGuildRecord> allowedGuilds = new List<AllowedGuildRecord>();

            try
            {
                foreach (DataRow row in sheetData.Tables[0].Rows)
                {
                    // Create a basic guild record.
                    string name = row["Primary Guild"] != DBNull.Value ? Convert.ToString(row["Primary Guild"]).Trim() : "";

                    if (string.IsNullOrEmpty(name))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. guild name is missing.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // munge the guild name into a slug.
                    string slug = guildService.CreateGuildSlug(name);

                    if (string.IsNullOrEmpty(slug))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Guild name cannot be converted into a valid guild slug.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // Get primary class type.
                    string primaryClass = row["Primary Class"] != DBNull.Value ? Convert.ToString(row["Primary Class"]).Trim() : "";

                    if (string.IsNullOrEmpty(primaryClass))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. guild primary class is missing.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    string magicColour = row["Magic Colour"] != DBNull.Value ? Convert.ToString(row["Magic Colour"]).Trim() : "";
                    string sphere = row["Sphere"] != DBNull.Value ? Convert.ToString(row["Sphere"]).Trim() : "";

                    // Create guild record.
                    Guild guild = guildService.AddGuild(ruleSetId, name, slug, primaryClass, "");

                    if (!string.IsNullOrEmpty(magicColour))
                        guild.MagicColour = magicColour;

                    if (!string.IsNullOrEmpty(sphere))
                        guild.Sphere = sphere;

                    // process each column excluding the three already processed and convert into an allowed class relationship if cell contains TRUE.
                    foreach (DataColumn column in sheetData.Tables[0].Columns)
                    {
                        if (column.ColumnName == "Primary Guild" || column.ColumnName == "Primary Class" || column.ColumnName == "Magic Colour" || column.ColumnName == "Sphere")
                            continue;

                        bool allowed = row[column.ColumnName] != DBNull.Value && Convert.ToBoolean(row[column.ColumnName]);

                        if (!allowed)
                            continue;

                        string allowedGuildName = column.ColumnName.Trim();
                        string allowedGuildSlug = guildService.CreateGuildSlug(allowedGuildName);

                        if (!string.IsNullOrEmpty(allowedGuildName))
                        {
                            allowedGuilds.Add(new AllowedGuildRecord()
                            {
                                PrimaryGuild = guild,
                                AllowedGuildSlug = allowedGuildSlug
                            });
                        }
                    }
                }

                // Save all guild records.
                context.Save();

                // Process all allowed sub guild records.
                foreach (AllowedGuildRecord record in allowedGuilds)
                {
                    Guild allowedGuild = guildService.GetGuild(ruleSetId, record.AllowedGuildSlug);

                    if (allowedGuild == null)
                    {
                        result.Errors.Add(string.Format("Invalid sub guild {0} for primary guild {1}.", record.AllowedGuildSlug, record.PrimaryGuild.Name));
                        if (breakOnFailedRow) break; else continue;
                    }

                    if (record.PrimaryGuild.AllowedGuilds == null)
                        record.PrimaryGuild.AllowedGuilds = new List<Guild>();

                    record.PrimaryGuild.AllowedGuilds.Add(allowedGuild);
                }

                context.Save();
            }
            catch (Exception ex)
            {
                result.Errors.Add(string.Format("Critical import error at row {0}. Exception: {1}.  The import could not continue.", rowIndex, ex.Message));
                result.Success = false;
            }

            result.Success = true;
            return result;
        }
        #endregion

        #region ImportInvocations
        public ImportResult ImportInvocations(IUnitOfWork context, string spreadsheetPath, bool breakOnFailedRow, int ruleSetId)
        {
            ImportResult result = new ImportResult();

            // Check the file exists.
            if (!File.Exists(spreadsheetPath))
            {
                result.Errors.Add(string.Format("The import file {0} does not exist.", spreadsheetPath));
                return result;
            }

            // Get the spreadsheet data from the first sheet.
            ExcelRepository excel = new ExcelRepository();

            DataSet sheetData = excel.ReadFile(spreadsheetPath);

            IInvocationService invocationService = new InvocationService(context);
            IGuildService guildService = new GuildService(context);

            int rowIndex = 0;

            List<GuildAssignmentRecord> guildAssignments = new List<GuildAssignmentRecord>();

            try
            {
                foreach (DataRow row in sheetData.Tables[0].Rows)
                {
                    // Create a basic invocation record.
                    string invocationName = row["Spell Name"] != DBNull.Value ? Convert.ToString(row["Spell Name"]).Trim() : "";

                    if (string.IsNullOrEmpty(invocationName))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Invocation name is missing.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // munge the skill name into a slug.
                    string slug = invocationService.CreateInvocationSlug(invocationName);

                    if (string.IsNullOrEmpty(slug))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Invocation name cannot be converted into a valid invocation slug.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // range is optional
                    string range = row["Range"] != DBNull.Value ? Convert.ToString(row["Range"]).Trim() : "";

                    // description is optional
                    string description = row["Description"] != DBNull.Value ? Convert.ToString(row["Description"]).Trim() : "";

                    // duration is optional
                    string duration = row["Duration"] != DBNull.Value ? Convert.ToString(row["Duration"]).Trim() : "";

                    // family is required.
                    string family = row["Family"] != DBNull.Value ? Convert.ToString(row["Family"]).Trim() : "";

                    if (string.IsNullOrEmpty(family))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Invocation family is missing.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // sphere
                    Sphere sphere = Sphere.Evil;

                    string sphereString = row["Sphere"] != DBNull.Value ? Convert.ToString(row["Sphere"]).Trim() : "";

                    if (!Enum.TryParse<Sphere>(sphereString, out sphere))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Invocation sphere is not valid.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }


                    // choice group is optional.
                    string choiceGroup = row["Choice Group"] != DBNull.Value ? Convert.ToString(row["Choice Group"]).Trim() : "";

                    // spell level is required.
                    string levelString = row["Level"] != DBNull.Value ? Convert.ToString(row["Level"]).Trim() : "";

                    if (string.IsNullOrEmpty(levelString))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell level is missing.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    int level = 0;

                    if (!int.TryParse(levelString, out level))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell level is invalid.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // extendable is optional.
                    string extendableString = row["Extendable"] != DBNull.Value ? Convert.ToString(row["Extendable"]).Trim() : "";

                    bool extendable = false;

                    if (!string.IsNullOrEmpty(extendableString) && !bool.TryParse(extendableString, out extendable))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell extendable flag is invalid.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // specialist is optional.
                    string specialistString = row["Specialist"] != DBNull.Value ? Convert.ToString(row["Specialist"]).Trim() : "";

                    bool specialist = false;

                    if (!string.IsNullOrEmpty(specialistString) && !bool.TryParse(specialistString, out specialist))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell specialist flag is invalid.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // common is optional.
                    string commonString = row["Common Invocation"] != DBNull.Value ? Convert.ToString(row["Common Invocation"]).Trim() : "";

                    bool common = false;

                    if (!string.IsNullOrEmpty(commonString) && !bool.TryParse(commonString, out common))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell common flag is invalid.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }


                    Invocation invocation = invocationService.AddInvocation(ruleSetId, slug, invocationName, description, range, duration, extendable, level, sphere, common, choiceGroup, family);

                    // harvest any requirements.
                    string requiresString = row["Requires"] != DBNull.Value ? Convert.ToString(row["Requires"]).Trim() : "";

                    if (!string.IsNullOrEmpty(requiresString))
                    {
                        invocation.RequiredGroupName = invocationService.CreateInvocationSlug(requiresString.Trim());
                    }

                    // harvest any guild assignments.
                    string[] guilds = new string[4];

                    guilds[0] = row["Guild 1"] != DBNull.Value ? Convert.ToString(row["Guild 1"]).Trim() : "";
                    guilds[1] = row["Guild 2"] != DBNull.Value ? Convert.ToString(row["Guild 2"]).Trim() : "";
                    guilds[2] = row["Guild 3"] != DBNull.Value ? Convert.ToString(row["Guild 3"]).Trim() : "";
                    guilds[3] = row["Guild 4"] != DBNull.Value ? Convert.ToString(row["Guild 4"]).Trim() : "";

                    foreach (string guildName in guilds)
                    {
                        string guildSlug = guildService.CreateGuildSlug(guildName);

                        Guild guild = guildService.GetGuild(ruleSetId, guildSlug);

                        if (guild != null)
                            guildAssignments.Add(new GuildAssignmentRecord() { Guild = guild, RequiringInvocation = invocation });
                    }
                }

                context.Save();


                // Now process any guild assignments
                foreach (GuildAssignmentRecord record in guildAssignments)
                {
                    if (record.Guild == null || record.RequiringInvocation == null || record.RequiringInvocation.InvocationID <= 0)
                        continue;

                    invocationService.AddGuild(record.RequiringInvocation.InvocationID, record.Guild);
                }

                context.Save();
            }
            catch (Exception ex)
            {
                result.Errors.Add(string.Format("Critical import error at row {0}. Exception: {1}.  The import could not continue.", rowIndex, ex.Message));
                result.Success = false;
            }

            result.Success = true;
            return result;
        }
        #endregion

        #region ImportSpells
        public ImportResult ImportSpells(IUnitOfWork context, string spreadsheetPath, bool breakOnFailedRow, int ruleSetId)
        {
            ImportResult result = new ImportResult();

            // Check the file exists.
            if (!File.Exists(spreadsheetPath))
            {
                result.Errors.Add(string.Format("The import file {0} does not exist.", spreadsheetPath));
                return result;
            }

            // Get the spreadsheet data from the first sheet.
            ExcelRepository excel = new ExcelRepository();

            DataSet sheetData = excel.ReadFile(spreadsheetPath);

            // Process and convert the input data.
            ISpellService spellService = new SpellService(context);

            int rowIndex = 0;

            try
            {
                result.TotalRows = sheetData.Tables[0].Rows.Count;

                foreach (DataRow row in sheetData.Tables[0].Rows)
                {
                    // Create a basic spell record.
                    string spellName = row["Spell Name"] != DBNull.Value ? Convert.ToString(row["Spell Name"]).Trim() : "";

                    if (string.IsNullOrEmpty(spellName))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell name is missing.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // munge the skill name into a slug.
                    string slug = spellService.CreateSpellSlug(spellName);

                    if (string.IsNullOrEmpty(slug))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell name cannot be converted into a valid spell slug.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // legacy name is optional
                    //string legacyName = row["Legacy"] != DBNull.Value ? Convert.ToString(row["Legacy"]) : "";

                    // range is optional
                    string range = row["Range"] != DBNull.Value ? Convert.ToString(row["Range"]).Trim() : "";

                    // description is optional
                    string description = row["Description"] != DBNull.Value ? Convert.ToString(row["Description"]).Trim() : "";

                    // duration is optional
                    string duration = row["Duration"] != DBNull.Value ? Convert.ToString(row["Duration"]).Trim() : "";

                    // family is required.
                    string family = row["Family"] != DBNull.Value ? Convert.ToString(row["Family"]).Trim() : "";

                    if (string.IsNullOrEmpty(family))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell family is missing.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    family = spellService.CreateSpellSlug(family);

                    // colour is required.
                    string colour = row["Colour"] != DBNull.Value ? Convert.ToString(row["Colour"]).Trim() : "";

                    SpellColour spellColour = SpellColour.Black;

                    if (string.IsNullOrEmpty(colour))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell colour is missing.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    if (!Enum.TryParse<SpellColour>(colour, out spellColour))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell colour is not valid.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // choice group is optional.
                    string choiceGroup = row["Choice Group"] != DBNull.Value ? Convert.ToString(row["Choice Group"]).Trim() : "";

                    // spell level is required.
                    string levelString = row["Level"] != DBNull.Value ? Convert.ToString(row["Level"]).Trim() : "";

                    if (string.IsNullOrEmpty(levelString))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell level is missing.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    int level = 0;

                    if (!int.TryParse(levelString, out level))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell level is invalid.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // extendable is optional.
                    string extendableString = row["Extendable"] != DBNull.Value ? Convert.ToString(row["Extendable"]).Trim() : "";

                    bool extendable = false;

                    if (!bool.TryParse(extendableString, out extendable))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell extendable flag is invalid.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // specialist is optional.
                    string specialistString = row["Specialist"] != DBNull.Value ? Convert.ToString(row["Specialist"]).Trim() : "";

                    bool specialist = false;

                    if (!bool.TryParse(specialistString, out specialist))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell specialist flag is invalid.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // primary is optional.
                    string primaryString = row["Primary Only"] != DBNull.Value ? Convert.ToString(row["Primary Only"]).Trim() : "";

                    bool primary = false;

                    if (!bool.TryParse(primaryString, out primary))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell primary flag is invalid.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // common is optional.
                    string commonString = row["Common Spell"] != DBNull.Value ? Convert.ToString(row["Common Spell"]).Trim() : "";

                    bool common = false;

                    if (!bool.TryParse(commonString, out common))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell common flag is invalid.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // store type is optional
                    StoreType store = StoreType.None;

                    string storeString = row["Store"] != DBNull.Value ? Convert.ToString(row["Store"]).Trim() : "";

                    if (!string.IsNullOrEmpty(storeString) && !Enum.TryParse<StoreType>(storeString, out store))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Spell store value is invalid.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    Spell spell = spellService.AddSpell(ruleSetId, slug, spellName, description, range, duration, extendable, level, spellColour, specialist, store, primary, common, choiceGroup, family);

                    // harvest any requirements.
                    string requiresString = row["Requires"] != DBNull.Value ? Convert.ToString(row["Requires"]).Trim() : "";

                    if (!string.IsNullOrEmpty(requiresString))
                    {
                        spell.RequiredGroupName = spellService.CreateSpellSlug(requiresString.Trim());
                    }
                }
                
                context.Save();
            }
            catch (Exception ex)
            {
                result.Errors.Add(string.Format("Critical import error at row {0}. Exception: {1}.  The import could not continue.", rowIndex, ex.Message));
                result.Success = false;
            }

            result.Success = true;
            return result;
        }
        #endregion

        #region ImportSkills
        /// <summary>
        /// Import skills from a spreadsheet.
        /// </summary>
        /// <param name="context">The work context</param>
        /// <param name="spreadsheetPath">The local path to the spreadsheet (.xls|.xlsx)</param>
        /// <param name="breakOnFailedRow">Whether to break the import on an individual failed row or continue.</param>
        /// <param name="ruleSetId">The id of the rule set to add the skill to.</param>
        /// <returns>The import results.</returns>
        public ImportResult ImportSkills(IUnitOfWork context, string spreadsheetPath, bool breakOnFailedRow, int ruleSetId)
        {
            ImportResult result = new ImportResult();

            // Check the file exists.
            if (!File.Exists(spreadsheetPath))
            {
                result.Errors.Add(string.Format("The import file {0} does not exist.", spreadsheetPath));
                return result;
            }

            // Get the spreadsheet data from the first sheet.
            ExcelRepository excel = new ExcelRepository();

            DataSet sheetData = excel.ReadFile(spreadsheetPath);

            // Process and convert the input data.
            int rowIndex = 0;

            ISkillService skillService = new SkillService(context);
            IRulesService rulesService = new RulesService(context);

            List<SkillRequirementRecord> requirements = new List<SkillRequirementRecord>();

            try
            {
                result.TotalRows = sheetData.Tables[0].Rows.Count;

                foreach (DataRow row in sheetData.Tables[0].Rows)
                {
                    /*
                     *  1. Create the basic skill record.
                     */
                    string skillName = row["Name"] != DBNull.Value ? Convert.ToString(row["Name"]).Trim() : "";

                    if (string.IsNullOrEmpty(skillName))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Skill name is missing.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // munge the skill name into a slug.
                    string slug = skillService.CreateSkillSlug(skillName);

                    if (string.IsNullOrEmpty(slug))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Skill name cannot be converted into a valid skill slug.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // legacy name is optional
                    string legacyName = row["Legacy"] != DBNull.Value ? Convert.ToString(row["Legacy"]) : "";

                    // skill type name.
                    string skillType = row["Type"] != DBNull.Value ? Convert.ToString(row["Type"]) : "";

                    if (string.IsNullOrEmpty(skillType))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Skill type is required.", rowIndex));
                        if (breakOnFailedRow) break; else continue;
                    }

                    // convert type to skill type enum.
                    SkillType type = (SkillType) Enum.Parse(typeof(SkillType), skillType);

                    // Get any max ranks.
                    int? maxRanks = null;

                    string maxPicks = row["Max Picks"] != DBNull.Value ? Convert.ToString(row["Max Picks"]) : "";

                    int max = 0;
                    if (int.TryParse(maxPicks, out max))
                        maxRanks = max;

                    // Get any status cost.
                    int? statusCost = null;

                    string status = row["Status Cost"] != DBNull.Value ? Convert.ToString(row["Status Cost"]) : "";

                    int stCost = 0;
                    if (int.TryParse(status, out stCost))
                        statusCost = stCost;

                    // Get whether a basic skill.
                    bool isBasic = false;

                    // Create the basic skill record.
                    Skill skill = skillService.AddSkill(ruleSetId, skillName, maxRanks, isBasic, slug, type);
                    skill.LegacyName = legacyName;
                    skill.Status = statusCost;

                    if (skill.Costs == null)
                    {
                        skill.Costs = new List<SkillCost>();
                    }

                    /*
                     *  2. Create skill costs against points tables.
                     */

                    // points columns
                    string[] columnNames = new string[] 
                    { 
                        "Human Warrior", "Human Acolyte", "Human Mage", "Human Scout", 
                        "Elf Warrior", "Elf Acolyte", "Elf Mage", "Elf Scout",
                        "Half-orc Warrior", "Half-orc Acolyte", "Half-orc Mage", "Half-orc Scout"
                    };

                    foreach (string columnName in columnNames)
                    {
                        AddSkillCost(ref result, skill, columnName, row, rulesService, rowIndex);
                    }


                    /*
                     *  3. Create guild requirements.
                     */
                    string[] guildColumns = new string[] 
                    {
                        "Archers", "The Wolfhold Scouts", "Barbarians", "Black School of Magic", "Blue School of Magic", "Temple of Earth Magic",
                        "Crusaders", "Dark brethren", "Dark path", "Dark seers", "Druids", "Fell Knights of Ushaz",
                        "Green School of Magic", "Grey gauntlet", "Grey path", "Grey School of Magic", "Grey wardens",
                        "Hospitallers", "Humacti", "The Iron Guard", "The Knights Martial", "Michelines", "The Monastery", "Order of Saint Michel", "The Pathfinders", 
                        "The Rangers", "Reapers", "Red School of Magic", "The Seekers",
                        "White path", "White School of Magic", "Yellow guild"
                        //,"Necromancers"
                    };

                    IGuildService guildService = new GuildService(context);

                    foreach (string guildColumn in guildColumns)
                    {
                        AddGuildRequirement(guildColumn, skill, rowIndex, row, guildService, ref result, ruleSetId, skillService);
                    }

                    /*
                     *  4. Create skill requirements.
                     *     Accumulate all the requirements for each skill here but only process then once
                     *     all the skills have been added.
                     */
                    string reqs = row["Requirements"] != DBNull.Value ? Convert.ToString(row["Requirements"]).ToLower() : "";

                    if (!string.IsNullOrEmpty(reqs) && reqs.ToLower() != "none")
                    {
                        // try and split out the && requirements.
                        if (reqs.Contains("&&"))
                        {
                            reqs = reqs.Replace("&&", "&");

                            // split out the all skills/section.
                            string[] sections = reqs.Split('&');

                            if (sections != null) // just one skill.
                            {
                                foreach (string section in sections)
                                {
                                    if (section.Contains("||"))
                                    {
                                        string sectionFixed = section.Replace("||", "|");

                                        // Check for || markers. 
                                        string[] subSections = sectionFixed.Split('|');

                                        if (subSections != null)
                                        {
                                            foreach (string subSection in subSections)
                                                requirements.Add(BuildRequirementRecord(subSection, skill, RequirementCombination.OR, skillService));
                                        }
                                    }
                                    else
                                    {
                                        // Create an AND requirement.
                                        requirements.Add(BuildRequirementRecord(section, skill, RequirementCombination.AND, skillService));
                                    }
                                }
                            }
                        }
                        else if (reqs.Contains("||"))
                        {
                            string sectionFixed = reqs.Replace("||", "|");

                            // Check for || markers. 
                            string[] subSections = sectionFixed.Split('|');

                            if (subSections != null)
                            {
                                foreach (string subSection in subSections)
                                    requirements.Add(BuildRequirementRecord(subSection, skill, RequirementCombination.OR, skillService));
                            }
                       }
                        else
                        {
                            // Create an AND requirement.
                            requirements.Add(BuildRequirementRecord(reqs, skill, RequirementCombination.AND, skillService));
                        }
                    }

                    /*
                     *  5. Create any rank requirements.
                     */
                    string minRankValue = row["Required Rank"] != DBNull.Value ? Convert.ToString(row["Required Rank"]) : "";

                    if (!string.IsNullOrEmpty(minRankValue.Trim()))
                    {
                        int value = 0;

                        if (int.TryParse(minRankValue.Trim(), out value))
                        {
                            if (value > 0)
                            {
                                requirements.Add(new SkillRequirementRecord() { 
                                    Combines = RequirementCombination.AND,
                                    RequiringSkill = skill,
                                    MinRank = value
                                });
                            }
                        }
                    }

                    /*
                     *  5. Create skill bonuses.
                     */
                    AddBonus(skillService, skill, rowIndex, row, ref result);

                    rowIndex++;
                }

                // Now all skill objects created commit them to the database.
                context.Save();

                // Now loop any skill requirements and create objects for them.
                foreach (SkillRequirementRecord record in requirements)
                {
                    int? requiredSkillId = null;

                    // Get skill required.
                    if (!string.IsNullOrEmpty(record.RequiredSkillSlug))
                    {
                        Skill requiredSkill = skillService.GetSkill(ruleSetId, record.RequiredSkillSlug);

                        if (requiredSkill == null)
                        {
                            result.Errors.Add(string.Format("Error adding skill requirement.  Unable to find required skill with slug {0}.", record.RequiredSkillSlug));
                            continue;
                        }

                        requiredSkillId = requiredSkill.SkillID;
                    }

                    skillService.CreateRequirement(record.RequiringSkill.SkillID, requiredSkillId, null, record.MinRank, false, record.RanksRequired, record.Combines, null);
                }

                // Now save all extra requirement objects.
                context.Save();
            }
            catch (Exception ex)
            {
                result.Errors.Add(string.Format("Critical import error at row {0}. Exception: {1}.  The import could not continue.", rowIndex, ex.Message));
                result.Success = false;
            }

            // Create a new skill (or update an old one based on table data).

            result.Success = true;
            return result;
        }
        #endregion


        #region protected/private methods

        #region AddGuildRequirement
        /// <summary>
        /// Check for and any any guild requirement based on a guild column for given row.
        /// Content values of 0 = no requirement, 1 = rank 20, 2 = rank 64, 3 = rank 128.
        /// </summary>
        /// <param name="columnName">The name of the guild column.</param>
        /// <param name="skill">The parent skill</param>
        /// <param name="rowIndex">The index of the import row.</param>
        /// <param name="row"></param>
        /// <param name="guildService"></param>
        /// <param name="result"></param>
        protected void AddGuildRequirement(string columnName, Skill skill, int rowIndex, DataRow row, IGuildService guildService, ref ImportResult result, int ruleSetId, ISkillService skillService)
        {
            string columnValue = row[columnName] != DBNull.Value ? Convert.ToString(row[columnName]).ToUpper() : "";

            if (string.IsNullOrEmpty(columnValue) || columnValue.ToLower() == "0")
                return; // no requirement on this guild.

            string slug = guildService.CreateGuildSlug(columnName);

            Guild guild = guildService.GetGuild(ruleSetId, slug);

            if (guild == null)
            {
                result.Errors.Add(string.Format("Error at row {0}.  Unable to find guild for column {1}.", rowIndex, columnName));
                return;
            }

            // convert column value to level.
            int? guildLevel = null;

            int value = 0;
            if (int.TryParse(columnValue, out value))
            {
                guildLevel = value;
            }

            if (!guildLevel.HasValue || guildLevel.Value <= 0 || guildLevel.Value > 3)
            {
                result.Errors.Add(string.Format("Error at row {0}.  Invalid guild level value {2} for column {1}.", rowIndex, columnName, columnValue));
                return;
            }

            // Create the guild requirement.
            skillService.CreateRequirement(skill, null, guild.GuildID, null, false, null, RequirementCombination.OR, guildLevel);
        }
        #endregion

        #region AddSkillCost
        /// <summary>
        /// Add a skill cost for a points table.
        /// </summary>
        /// <param name="result">The import result object.</param>
        /// <param name="skill">The parent skill.</param>
        /// <param name="columnName">The import column name like 'Human Warrior'.</param>
        /// <param name="row">The import row.</param>
        /// <param name="rulesService">The rules service.</param>
        /// <param name="rowIndex">The current row index.</param>
        protected void AddSkillCost(ref ImportResult result, Skill skill, string columnName, DataRow row, IRulesService rulesService, int rowIndex)
        {
            // find the points table id.

            // split class and race from column name.
            string[] parts = columnName.Split(' ');

            PointsTable table = rulesService.GetPointsTable(parts[1], parts[0]);

            // get column value
            string columnValue = row[columnName] != DBNull.Value ? Convert.ToString(row[columnName]).ToUpper() : "";
            int? cost = null;
            int? factor = 0;

            if (!string.IsNullOrEmpty(columnValue))
            {
                if (columnValue == "NO")
                    return;

                if (columnValue.Contains("R")) // complex type
                {
                    factor = 1; // flag this as a series type.

                    if (columnValue.Contains("*"))
                    {
                        // split off multiplier
                        string[] multiParts = columnValue.Split('*');

                        if (multiParts.Length != 2)
                        {
                            result.Errors.Add(string.Format("Error at row {0}. Invalid points formula {2} for column {1}.", rowIndex, columnName, columnValue));
                        }
                        else
                        {
                            // assume left part contains 'r'.
                            int multiplier = 0;
                            if (int.TryParse(multiParts[1].Trim(), out multiplier))
                            {
                                if (multiplier > 0)
                                    factor = multiplier;
                            }

                            if (multiParts[0].Trim().Contains("+"))
                            {
                                string[] plusParts = multiParts[0].Split('+');
                                int costVal = -1;

                                if (plusParts[0].Trim().Contains("R"))
                                    int.TryParse(plusParts[1].Trim(), out costVal);
                                else
                                    int.TryParse(plusParts[0].Trim(), out costVal);

                                if (costVal >= 0)
                                    cost = costVal;
                            }
                        }
                    }
                    else if (columnValue.Contains("+"))
                    {
                        string[] plusParts = columnValue.Split('+');
                        int costVal = 0;

                        if (plusParts[0].Trim().Contains("R"))
                            int.TryParse(plusParts[1].Trim(), out costVal);
                        else
                            int.TryParse(plusParts[0].Trim(), out costVal);

                        if (costVal > 0)
                            cost = costVal;
                    }
                }
                else
                {
                    int costVal = 0;
                    if (!int.TryParse(columnValue, out costVal))
                    {
                        result.Errors.Add(string.Format("Error at row {0}. Points cost for column {1} could not be converted to an int, assuming 0 cost.", rowIndex, columnValue));
                    }
                    else
                        cost = costVal;
                }

                skill.Costs.Add(new SkillCost() { PointsTableID = table.PointsTableID, Factor = factor, Cost = cost, Available = true });
            }
        }
        #endregion

        #region BuildRequirementRecord
        /// <summary>
        /// Create a skill requirement record.
        /// </summary>
        /// <param name="section">The text description of the skill requirement.</param>
        /// <param name="skill">The skill with the requirement.</param>
        /// <param name="combines">How the requirement combines with other requirements.</param>
        /// <param name="skillService">The skill service.</param>
        /// <returns>The skill requirement record.</returns>
        protected SkillRequirementRecord BuildRequirementRecord(string section, Skill skill, RequirementCombination combines, ISkillService skillService)
        {
            int? ranksRequired = null;

            // Check for min ranks.
            int openIndex = section.IndexOf('(');
            int closeIndex = section.IndexOf(')');

            if (openIndex > -1 && closeIndex > openIndex)
            {
                string number = section.Substring(openIndex + 1, closeIndex - openIndex - 1);

                int ranks = 0;
                if (int.TryParse(number, out ranks))
                    ranksRequired = ranks;
            }

            // Create required skill slug.
            string skillName = section;

            if (openIndex > 0)
                skillName = skillName.Substring(0, openIndex);

            string slug = skillService.CreateSkillSlug(skillName);

            // Create an OR requirement.
            SkillRequirementRecord req = new SkillRequirementRecord()
            {
                Combines = combines,
                RanksRequired = ranksRequired,
                RequiredSkillSlug = slug,
                RequiringSkill = skill
            };

            return req;
        }
        #endregion

        #region AddBonus
        /// <summary>
        /// Process a bonus column value.
        /// </summary>
        /// <param name="skill">The parent skill.</param>
        /// <param name="rowIndex">The index of the row we are processing.</param>
        /// <param name="row">The row iteself.</param>
        /// <param name="results">The import results object.</param>
        protected void AddBonus(ISkillService skillService, Skill skill, int rowIndex, DataRow row, ref ImportResult results)
        {
            string columnValue = row["Bonus"] != DBNull.Value ? Convert.ToString(row["Bonus"]) : "";

            if (string.IsNullOrEmpty(columnValue) || columnValue.ToLower() == "0")
                return; // no bonuses for this skill.

            // check for delimiter.
            string[] bonusList = columnValue.Split('&');

            if (bonusList.Length <= 0)
                return;

            foreach (string bonusString in bonusList)
            {
                string bonusPart = bonusString;
                string requirement = string.Empty;

                if (bonusString.Contains(":"))
                {
                    string[] parts = bonusString.Split(':');

                    if (parts.Length == 2)
                    {
                        bonusPart = parts[0];
                        requirement = parts[1].Trim();
                    }
                }

                int openIndex = bonusPart.IndexOf('(');
                int closeIndex = bonusPart.IndexOf(')');
                int quantity = 1;
                string bonusValue = null;

                if (openIndex > -1 && closeIndex > openIndex)
                {
                    string parenContent = bonusPart.Substring(openIndex + 1, closeIndex - openIndex - 1);

                    // optionally can be two numbers delimited by comma for qty and value.
                    string[] parenValues = parenContent.Split(',');

                    if (parenValues.Length <= 0)
                        continue;

                    if (parenValues.Length >= 1) // first must be qty.
                    {
                        int.TryParse(parenValues[0], out quantity);
                    }

                    if (parenValues.Length > 1) //second is value.
                    {
                        if (!string.IsNullOrEmpty(parenValues[1]))
                        {
                            bonusValue = parenValues[1];

                            string[] skills = null;

                            if (bonusValue.IndexOf('|') > 0)
                            {
                                skills = bonusValue.Split('|');
                            }
                            else
                                skills = new string[1] { bonusValue };

                            // try and convert to a skill name.
                            StringBuilder slugList = new StringBuilder();

                            foreach (string skillName in skills)
                            {
                                string slug = skillService.CreateSkillSlug(skillName);
                                Skill targetSkill = skillService.GetSkill(skill.RuleSetID, slug);

                                if (targetSkill != null)
                                {
                                    if (slugList.Length > 0)
                                        slugList.Append("|");

                                    slugList.Append(slug);
                                }
                            }

                            bonusValue = slugList.ToString();
                        }
                    }
                }

                string name = bonusString;

                if (openIndex > 0)
                    name = bonusString.Substring(0, openIndex);

                // convert name to enum value.
                BonusTypes type = BonusTypes.ArmouredDex;
                
                if (!Enum.TryParse<BonusTypes>(name, out type))
                {
                    results.Errors.Add(string.Format("Error at row {0}. Invalid bonus type {2} for column {1}.", rowIndex, name, columnValue));
                    continue;
                }

                // check requirement type.
                BonusRequirement reqType = BonusRequirement.EquippedArmour;

                if (!string.IsNullOrEmpty(requirement) && !Enum.TryParse(requirement, out reqType))
                {
                    results.Errors.Add(string.Format("Error at row {0}. Invalid bonus requirement {2} for column {1}.", rowIndex, name, requirement));
                    continue;
                }

                Bonus bonus = new Bonus() { 
                    Skill = skill,
                    Quantity = quantity,
                    Value = bonusValue,
                    Type = Enum.GetName(typeof(BonusTypes), type),
                    Requirement = requirement
                };

                if (skill.Bonuses == null)
                    skill.Bonuses = new List<Bonus>();

                skill.Bonuses.Add(bonus);
            }
        }
        #endregion

        #endregion
    }

    /// <summary>
    /// The result from an import operation.
    /// </summary>
    public class ImportResult {
        public bool Success { get; set; }
        public List<string> Errors { get; protected set; }
        public int TotalRows { get; set; }
        public int ImportedRows { get; set; }

        public ImportResult()
        {
            Success = false;
            Errors = new List<string>();
            ImportedRows = 0;
            TotalRows = 0;
        }
    }


    public class SkillRequirementRecord
    {
        public RequirementCombination Combines { get; set; }
        public string RequiredSkillSlug { get; set; }
        public Skill RequiringSkill { get; set; }
        public int? RanksRequired { get; set; }
        public int? MinRank { get; set; }
    }

    public class GuildAssignmentRecord
    {
        public Invocation RequiringInvocation { get; set; }
        public Guild Guild { get; set; }
    }

    public class AllowedGuildRecord
    {
        public Guild PrimaryGuild { get; set; }
        public string AllowedGuildSlug { get; set; }
    }
}